package com.manager.service.news;

import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import oracle.sql.CLOB;

import org.jorm.Session;

import com.build.SessionBuilder;
import com.web.model.Category;
import com.web.model.News;
import com.web.model.dao.CategoryDao;
import com.web.model.dao.NewsDao;
import com.web.model.dao.impl.CategoryDaoImpl;
import com.web.model.dao.impl.NewsDaoImpl;
import com.web.model.vo.query.NewsQuery;
import com.web.tag.bean.PageModel;

public class NewsService {

	public List<News> findList(PageModel model, NewsQuery query) {
		List<News> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			NewsDao newsDao = new NewsDaoImpl();
			String whereSql = " where 1=1 ";
			if (query.getClassid() != null && query.getClassid() > 0) {
				whereSql += " and " + News.COLUMN_CLASSID + " = " + query.getClassid();
			}
			if (query.getTitle() != null && !query.getTitle().trim().isEmpty()) {
				whereSql += " and " + News.COLUMN_TITLE + " like '%" + query.getTitle() + "%'";
			}
			String sql = "select " + News.COLUMN_ID + "," + News.COLUMN_TITLE + "," + News.COLUMN_ADDTIME + "," + News.COLUMN_CREATE_TIME + "," + News.COLUMN_STATE + " from "
					+ News.TABLE_NAME + " " + whereSql + " order by " + News.COLUMN_CREATE_TIME + " desc";
			//System.out.println(sql);
			if (model != null) {
				try {
					model.setRecodeZong(newsDao.getResultCount("select count(*) as ct from " + News.TABLE_NAME + " " + whereSql, 1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= " + row_x + ") where rownum_ > " + row_y;
			}

			list = newsDao.queryForList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public void saveOrUpdate(News news, String clobValue) {
		if (news.getId() == null || news.getId() <= 0) {
			save(news, clobValue);
		} else {
			update(news, clobValue);
		}
	}

	public int save(News news, String clobValue) {
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			if (news.getId() == null || news.getId() <= 0) {
				String sql = " select max(id)+1 from tw_news ";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				int id = 0;
				while (rs.next())
					id = rs.getInt(1);
				news.setId(id);
			}
			conn.setAutoCommit(false);
			String insertSql = "insert into " + "tw_news ( ID ,TITLE,DTITLE,ADDTIME,KEYWORD,CLASSID,CONTENT_HTML , CONTENT_TEXT  ,AUTHOR_ID,"
					+ "CREATE_TIME ,FROM_NAME, FROM_URL   ,BROWSE      , RECOM     ,TOP_STATE   ,STATE     ,SORTNO  ) "
					+ "values(?,?,?,?,?,?,empty_clob(),empty_clob(),?,sysdate,?,?,0,?,?,?,?)";

			ps = conn.prepareStatement(insertSql);
			ps.setInt(1, news.getId());
			ps.setString(2, news.getTitle());
			ps.setString(3, news.getDtitle());
			ps.setTimestamp(4, news.getAddtime());
			ps.setString(5, news.getKeyword());
			ps.setInt(6, news.getClassid() == null ? 0 : news.getClassid());
			/*
			 * ps.setClob(8,news.getContentText()); ps.setClob(7,
			 * news.getContentHtml());
			 */

			ps.setInt(7, news.getAuthorId() == null ? 0 : news.getAuthorId());
			// 创建时间默认系统时间
			ps.setString(8, news.getFromName());
			ps.setString(9, news.getFromUrl());
			// 浏览次数默认0
			ps.setInt(10, news.getRecom() == null ? 0 : news.getRecom());
			ps.setInt(11, news.getTopState() == null ? 0 : news.getTopState());
			ps.setInt(12, news.getState() == null ? 0 : news.getState());
			ps.setInt(13, news.getSortno() == null ? 0 : news.getSortno());
			ps.executeUpdate();

			String sqlblob = "select * from tw_news where id=" + news.getId() + " for update";
			PreparedStatement ps2 = conn.prepareStatement(sqlblob);
			ResultSet rs2 = ps2.executeQuery();
			System.out.println("开始操作clob存入:");
			while (rs2.next()) {

				CLOB clob = (oracle.sql.CLOB) rs2.getClob("CONTENT_HTML");
				Writer outstream = clob.getCharacterOutputStream();
				outstream.write(clobValue, 0, clobValue.length());
				outstream.close();
				ps2.execute("commit");
				System.out.println("完毕:");
			}
			if (rs2 != null)
				rs2.close();
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (ps2 != null)
				ps2.close();
			session.close();
		} catch (Exception e) {
			System.out.print(e);
			e.printStackTrace();
		}
		return 0;

	}

	public int update(News news, String clobValue) {
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			PreparedStatement ps = null;
			conn.setAutoCommit(false);
			String insertSql = "update tw_news set TITLE=?,DTITLE=?,ADDTIME=?,KEYWORD=?,CLASSID=?,AUTHOR_ID=?,"
					+ "CREATE_TIME=sysdate,FROM_NAME=?, FROM_URL=?, RECOM=?,TOP_STATE=?,STATE=?,SORTNO=? " + "where ID=?";

			ps = conn.prepareStatement(insertSql);
			ps.setString(1, news.getTitle());
			ps.setString(2, news.getDtitle());
			ps.setTimestamp(3, news.getAddtime());
			ps.setString(4, news.getKeyword());
			ps.setInt(5, news.getClassid() == null ? 0 : news.getClassid());
			ps.setInt(6, news.getAuthorId() == null ? 0 : news.getAuthorId());
			ps.setString(7, news.getFromName());
			ps.setString(8, news.getFromUrl());
			ps.setInt(9, news.getRecom() == null ? 0 : news.getRecom());
			ps.setInt(10, news.getTopState() == null ? 0 : news.getTopState());
			ps.setInt(11, news.getState() == null ? 0 : news.getState());
			ps.setInt(12, news.getSortno() == null ? 0 : news.getSortno());
			ps.setInt(13, news.getId());
			ps.executeUpdate();

			String sqlblob = "select * from tw_news where id=" + news.getId() + " for update";
			PreparedStatement ps2 = conn.prepareStatement(sqlblob);
			ResultSet rs2 = ps2.executeQuery();
			System.out.println("开始操作clob存入:");
			while (rs2.next()) {
				CLOB clob = (oracle.sql.CLOB) rs2.getClob("CONTENT_HTML");
				Writer outstream = clob.getCharacterOutputStream();
				outstream.write(clobValue, 0, clobValue.length());
				outstream.close();
				ps2.execute("commit");
				System.out.println("完毕:");
			}
			if (rs2 != null)
				rs2.close();
			if (ps != null)
				ps.close();
			if (ps2 != null)
				ps2.close();
			session.close();
		} catch (Exception e) {
			System.out.print(e);
			e.printStackTrace();
		}
		return 0;

	}

	public News getById(int id) {
		News obj = null;
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + News.TABLE_NAME + " where " + News.COLUMN_ID + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new News();
				obj.setAddtime(rs.getTimestamp(News.COLUMN_ADDTIME));
				obj.setAuthorId(rs.getInt(News.COLUMN_AUTHOR_ID));
				obj.setBrowse(rs.getInt(News.COLUMN_BROWSE));
				obj.setClassid(rs.getInt(News.COLUMN_CLASSID));
				obj.setContentHtml(rs.getClob(News.COLUMN_CONTENT_HTML));
				obj.setContentText(rs.getClob(News.COLUMN_CONTENT_TEXT));
				obj.setCreateTime(rs.getTimestamp(News.COLUMN_CREATE_TIME));
				obj.setDtitle(rs.getString(News.COLUMN_DTITLE));
				obj.setFromName(rs.getString(News.COLUMN_FROM_NAME));
				obj.setFromUrl(rs.getString(News.COLUMN_FROM_URL));
				obj.setId(rs.getInt(News.COLUMN_ID));
				obj.setKeyword(rs.getString(News.COLUMN_KEYWORD));
				obj.setRecom(rs.getInt(News.COLUMN_RECOM));
				obj.setSortno(rs.getInt(News.COLUMN_SORTNO));
				obj.setState(rs.getInt(News.COLUMN_STATE));
				obj.setTitle(rs.getString(News.COLUMN_TITLE));
				obj.setTopState(rs.getInt(News.COLUMN_TOP_STATE));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}

	public List<Category> findCates() {
		List<Category> cateList = null;
		try {
			Session session = SessionBuilder.oracleSession();
			CategoryDao cateDao = new CategoryDaoImpl();
			cateList = cateDao.queryForList("select * from " + Category.TABLE_NAME);
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cateList;
	}

	public void deleteNews(int id) {
		try {
			Session session = SessionBuilder.oracleSession();
			String sql = "delete from " + News.TABLE_NAME + " where " + News.COLUMN_ID + " = ?";
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
